﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Xml;
using System.Security.AccessControl;
using XQL;

namespace XQL
{
    public partial class XQLManagerStudio : Form
    {
        public string ServerName = "";
        public string ServerPath = "";
        public XQLManagerStudio()
        {
            InitializeComponent();
        }

        private void execQuery_Click(object sender, EventArgs e)
        {
            if (queryTxtBox.Text == "")
            {
                //if (File.Exists(ServerPath))
                //{
                //    XmlDocument xdoc = new XmlDocument();
                //    xdoc.Load("E:\\Projects\\XQL\\XQL\\DBTemp.xml");
                //    ListViewItem lt;
                //    ListViewItem ColumnListItem = new ListViewItem("");
                //    ListView ColumnList = new ListView();

                //    lt = new ListViewItem("");
                //    XmlNodeList xmlnode = xdoc.SelectNodes("RootList/FileList");
                //    foreach (XmlNode node1 in xmlnode)
                //    {
                //        foreach (XmlNode node2 in node1)
                //        {
                //            ColumnListItem = new ListViewItem(node2.Name.ToString());
                //            ColumnList.Items.Add(ColumnListItem);


                //        }
                //        break;
                //    }
                //    listView1.Clear();

                //    listView1.View = View.Details;


                //    foreach (XmlNode node1 in xmlnode)
                //    {
                //        foreach (XmlNode node2 in node1)
                //        {
                //            if (node2.Name == "FileName")
                //            {
                //                ColumnListItem = new ListViewItem(node2.InnerText.ToString());
                //            }
                //            else
                //                ColumnListItem.SubItems.Add(node2.InnerText.ToString());



                //        }
                //        listView1.Items.Add(ColumnListItem);


                //    }

                //    for (int i = 0; i < ColumnList.Items.Count; i++)
                //    {
                //        ColumnHeader colHead = new ColumnHeader();
                //        colHead.Text = "";
                //        colHead.Text = ColumnList.Items[i].Text;

                //        listView1.Columns.Add(colHead);






                //    }
                //    for (int i = 0; i < listView1.Columns.Count; i++)
                //    {
                //        listView1.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
                //        listView1.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
                //    }

                //    /*foreach (XmlNode tag in xmlnode)
                //    {
                //        XmlNodeList xmlnode2 = xdoc.GetElementsByTagName("FileList");
                //        foreach (XmlNode tag2 in xmlnode2)
                //        {

                //            foreach (XmlNode tag3 in tag2)
                //            {

                //                if (tag3.Name == "FileName")
                //                    lt = new ListViewItem(tag3.InnerText.ToString());
                //                else
                //                    lt.SubItems.Add(tag3.InnerText);


                //            }
                //            //    lt = new ListViewItem(tag2.InnerText);
                //            //if (tag2.Name == "Status")
                //            //    lt.SubItems.Add(tag2.InnerText);
                //            //if (tag2.Name == "FilePath")
                //            //    lt.SubItems.Add(tag2.InnerText);
                //            listView1.Items.Add(lt);

                //        }

                //    }*/

                //}
            }
            else
            {
                string query = queryTxtBox.Text;
                string[] queryList = query.Split(' ');
                foreach (string word in queryList)
                {

                    if (word == "")
                        word.Trim();
                    else
                    {
                        string word2 = word.ToLower();
                        if (word2.Contains("select") || word2.Contains("insert") || word2.Contains("delete"))
                        {
                            if (word2.Contains("select"))
                                selectQuery();
                            if (word2.Contains("insert"))
                                insertQuery();
                            if (word2.Contains("delete"))
                                deleteQuery();
                            break;
                        }
                        else
                        {
                            MessageBox.Show("Invalid Query");
                            break;
                        }
                    }
                         
                }
                
            }

        }

        private void queryTxtBox_TextChanged(object sender, EventArgs e)
        {
       
        }
        public void insertQuery()
        {
            string query = queryTxtBox.Text;
            string[] queryList = query.Split(' ');
            string TableName = "";
            string[] newValueList = null;
            bool validQuery = false;
            for (int i = 0; i < queryList.Length; i++)
            {
                if (i == 1)
                {
                    string secondQueryWord = queryList[i].ToLower();
                    if (secondQueryWord == "values")
                        continue;
                    else
                        break;
                }
                if (i == 2)
                {
                    string thirdQueryWord = queryList[i].ToLower();
                    if (thirdQueryWord == "into")
                        continue;
                    else
                        break;
                }
                if (i == 3)
                {
                    TableName = queryList[i];
                    if (TableName != "")
                        continue;
                    else
                        break;
                }
                if (i == 4)
                {
                    string fivthQueryWord = queryList[i];
                    if (fivthQueryWord.Contains('[') && fivthQueryWord.Contains(']'))
                    {
                        char[] valueList = fivthQueryWord.ToCharArray();
                        int lastColumnValue = valueList.Length - 2;
                        if (valueList[0].ToString() == "[")
                        {
                            if (valueList[1].ToString() == "," || valueList[lastColumnValue].ToString() == ",")
                            {
                               // MessageBox.Show("Invalid Query");
                                break;
                            }
                            newValueList = fivthQueryWord.Split(',','[',']');
                            for (int k = 1; k < newValueList.Length - 1; k++)
                            {
                                if (newValueList[k] == "" || newValueList[k].Contains(' '))
                                {
                                    validQuery = false;
                                    break;
                                }
                                else
                                    validQuery = true;
                            }
                        }
                        else
                        {
                            //MessageBox.Show("Invalid Query");
                            break;
                        }
                    }
                    else
                    {
                        MessageBox.Show("Invalid Query");
                        return;
                    }
                }
            }
            if (!validQuery)
                MessageBox.Show("Invalid Query");
            if (validQuery)
            {
                if(Directory.Exists(ServerPath))
                {
                    if (File.Exists(ServerPath + "\\" + TableName + ".xml"))
                    {
                        XmlDocument Table = new XmlDocument();
                        XmlElement ColumnName = Table.CreateElement(TableName);
                        bool NewRow = false;
                        Table.Load(ServerPath + "\\" + TableName + ".xml");
                        string NodeHier = "Root" + TableName;
                        string NodeHier2 = "Root" + TableName + "/" + TableName;
                        XmlNodeList TableNode2 = Table.SelectNodes(NodeHier2);
                        XmlNodeList TableNodes = Table.SelectNodes(NodeHier);
                        int ColumnCount = 0;

                        foreach (XmlNode node1 in TableNode2)
                        {
                            foreach (XmlNode node2 in node1)
                            {
                                //ColumnNamesList[ColumnCount] = node2.InnerText;
                                ColumnCount++;
                            }
                            break;
                        }
                        string[] ColumnNamesList = new string[ColumnCount];
                        int l = 0;
                        foreach (XmlNode node1 in TableNode2)
                        {
                            foreach (XmlNode node2 in node1)
                            {
                                ColumnNamesList[l] = node2.Name;
                                l++;
                            }
                            break;
                        }
                    ReloadNode: TableNodes = Table.SelectNodes(NodeHier);
                        //int enteredLength = ;
                        if (ColumnCount == newValueList.Length - 2)
                        {

                            foreach (XmlNode node1 in TableNodes)
                            {

                                l = 0;
                                if (!NewRow)
                                {
                                    foreach (XmlNode node2 in node1)
                                    {
                                        int k = 1;
                                        foreach (XmlNode node3 in node1)
                                        {
                                            if (node3.InnerText == "")
                                            {
                                                if (k < newValueList.Length - 1)
                                                {
                                                    XmlText ColumnValue = Table.CreateTextNode(newValueList[k].ToString());
                                                    node2.AppendChild(ColumnValue);
                                                    //node2.InnerText = newValueList[k].ToString();
                                                    k++;

                                                }
                                            }
                                            else
                                            {
                                                NewRow = true;
                                                break;
                                            }
                                      
                                           
                                        }
                                        NewRow = true;
                                        goto ReloadNode;

                                    }
                                }
                                else
                                {
                                    XmlElement TableMain = Table.CreateElement(TableName);
                                    for (l = 0; l < ColumnNamesList.Length; l++)
                                    {

                                        XmlElement TableColumn = Table.CreateElement(ColumnNamesList[l].ToString());
                                        XmlText TableColumnText = Table.CreateTextNode(newValueList[l+1]);
                                        TableColumn.AppendChild(TableColumnText);
                                        TableMain.AppendChild(TableColumn);
                                    }
                                    node1.AppendChild(TableMain);
                                }

                            }

                            
                            Table.Save(ServerPath + "\\" + TableName + ".xml");
                            
                        }
                        else
                        {
                            MessageBox.Show("Entered values in Column Parameter List is not matching with the actual number of columns!");
                            return;
                        }
                    }
                    else
                    {
                        MessageBox.Show("Table '" + TableName + "' does not exist!");
                        return;
                    }
                }
                //MessageBox.Show("Valid Query");
            }
        }
        public void deleteQuery()
        {

        }
        public void selectQuery()
        {
            string query = queryTxtBox.Text;
            string[] queryList = query.Split(' ');
            string TableName = "";
            string WhereColumnName = "";
            string WhereConditionValue = "";
            bool WhereEnable = false;
            bool validQuery = false;
            for (int i = 0; i < queryList.Length; i++)
            {
                if (i == 1)
                {
                    string secondQueryWord = queryList[i].ToLower();
                    if (secondQueryWord == "*")
                    {
                        continue;
                    }
                    else
                        break;
                }
                if (i == 2)
                {
                    string thirdQueryWord = queryList[i].ToLower();
                    if (thirdQueryWord == "from")
                    {
                        continue;
                    }
                    else
                        break;
                }
                if (i == 3)
                {
                    string fourthQueryWord = queryList[i];
                    if (fourthQueryWord != "")
                    {
                        TableName = fourthQueryWord;
                        if (i == queryList.Length - 1)
                        {
                            validQuery = true;
                            break;
                        }
                    }
                    else
                        break;
                }
                if (i == 4)
                {
                    string fivthQueryWord = queryList[i].ToLower();
                    if (fivthQueryWord == "where")
                    {
                        WhereEnable = true;
                        continue;
                    }
                    else
                        break;
                }
                if (i == 5)
                {
                    string sixthQueryWord = queryList[i].ToLower();
                    if (sixthQueryWord != "")
                    {
                        WhereColumnName = queryList[i].Trim();
                    }
                    else
                        break;
                }
                if (i == 6)
                {
                    string seventhQueryWord = queryList[i].ToLower();
                    if (WhereEnable)
                    {
                        if (seventhQueryWord == "=")
                            continue;
                        else
                            break;
                    }
                    else
                        break;
                }
                if (i == 7)
                {
                    string eighthQueryWord = queryList[i].ToLower();
                    if (eighthQueryWord != "")
                    {
                        WhereConditionValue = queryList[i].Trim();
                        if (i == queryList.Length - 1)
                        {
                            validQuery = true;
                            break;
                        }
                    }
                    else
                        break;
                }
                //if (queryList[i].ToLower() == TableName)
                //{
                //    validQuery = true;
                //    break;
                //}
                    
            }
            if (validQuery)
            {
                //MessageBox.Show(WhereColumnName);
                //MessageBox.Show(WhereConditionValue);
                string setTablePath = ServerPath + "\\" + TableName + ".xml";
                if (WhereEnable)
                {
                    if (File.Exists(setTablePath))
                    {
                        XmlDocument xdoc = new XmlDocument();
                        xdoc.Load(setTablePath);
                        ListViewItem lt;
                        ListViewItem ColumnListItem = new ListViewItem("");
                        ListView ColumnList = new ListView();
                        lt = new ListViewItem("");
                        bool validColumn = false;
                        string NodeHier = "Root" + TableName + "/" + TableName;
                        XmlNodeList xmlnode = xdoc.SelectNodes(NodeHier);
                        int ColumnIndex = 0;
                        int WhereColumnIndex = 0;
                        foreach (XmlNode node1 in xmlnode)
                        {
                            foreach (XmlNode node2 in node1)
                            {
                                ColumnIndex++;
                                if (node2.Name.ToString() == WhereColumnName)
                                {
                                    WhereColumnIndex = ColumnIndex;
                                    validColumn = true;
                                    break;
                                }
                                
                            }
                            break;
                        }
                        if (validColumn)
                        {
                            foreach (XmlNode node1 in xmlnode)
                            {
                                foreach (XmlNode node2 in node1)
                                {
                                    ColumnListItem = new ListViewItem(node2.Name.ToString());
                                    ColumnList.Items.Add(ColumnListItem);


                                }
                                break;
                            }
                            listView1.Clear();

                            listView1.View = View.Details;
                            int NoOfColumns = ColumnList.Items.Count;
                            ColumnListItem = new ListViewItem("");
                            foreach (XmlNode node1 in xmlnode)
                            {
                                foreach (XmlNode node2 in node1)
                                {
                                    if (node2.Name == ColumnList.Items[0].Text)
                                    {
                                        if(node2.InnerText.ToString() != "")
                                        ColumnListItem = new ListViewItem(node2.InnerText.ToString());
                                    }
                                    else
                                        if (node2.InnerText.ToString() != "")
                                        ColumnListItem.SubItems.Add(node2.InnerText.ToString());



                                }
                                listView1.Items.Add(ColumnListItem);


                            }
                            ListView FinalList = new ListView();
                            ListViewItem FinalListItem = new ListViewItem();
                           // int[] FinalIndexList = new int[listView1.Items.Count];
                            ListView FinalIndexList2 = new ListView();
                            ListViewItem FinalIndexListItem2 = new ListViewItem("");
                            int j = 0;
                            bool FailSearch = true;
                            for (int i = 0; i < listView1.Items.Count; i++)
                                {
                                    if (listView1.Items[i].SubItems[WhereColumnIndex - 1].Text == WhereConditionValue)
                                    {
                                        FinalIndexListItem2 = new ListViewItem(i.ToString());
                                        FinalIndexList2.Items.Add(FinalIndexListItem2);
                                    }
                                   
                                }

                            if (FinalIndexList2.Items.Count != 0)
                                FailSearch = false;
                            else
                                FailSearch = true;
                            j=0;
                            foreach (ListViewItem x in listView1.Items)
                            {
                               FinalList.Items.Add((ListViewItem)x.Clone());
                            }
                            listView1.Items.Clear();
                            if (FailSearch)
                            {
                                MessageBox.Show("No Records Found!");
                                listView1.Items.Clear();
                                return;
                            }
                            int y = 0;
                            foreach (ListViewItem x in FinalIndexList2.Items)
                            {
                                y++;
                            }

                            for (int i = 0; i < FinalList.Items.Count; i++)
                            {
                                if (y>=j+1)
                                {
                                    if (i.ToString() == FinalIndexList2.Items[j].Text)
                                    {
                                        FinalListItem = new ListViewItem(FinalList.Items[i].Text);
                                        for (int k = 1; k < NoOfColumns; k++)
                                        {
                                            FinalListItem.SubItems.Add(FinalList.Items[i].SubItems[k].Text);
                                        }
                                        listView1.Items.Add(FinalListItem);
                                        j++;
                                    }
                                }
                               
                            }
                            
                            

                            for (int i = 0; i < ColumnList.Items.Count; i++)
                            {
                                ColumnHeader colHead = new ColumnHeader();
                                colHead.Text = "";
                                colHead.Text = ColumnList.Items[i].Text;
                                listView1.Columns.Add(colHead);
                            }
                            for (int i = 0; i < listView1.Columns.Count; i++)
                            {
                                listView1.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
                                listView1.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
                            }

                        }
                        else
                        {
                            MessageBox.Show("'" + WhereColumnName + "' column is not available in the table '" + TableName + "'");
                            return;
                        }
                    }
                    else
                        MessageBox.Show("'" + TableName + "' table is not available");
                    return;
                }
                
                if (File.Exists(setTablePath))
                {
                    XmlDocument xdoc = new XmlDocument();
                    xdoc.Load(setTablePath);
                    ListViewItem lt;
                    ListViewItem ColumnListItem = new ListViewItem("");
                    ListView ColumnList = new ListView();

                    lt = new ListViewItem("");
                    string NodeHier = "Root" + TableName + "/" + TableName;
                    XmlNodeList xmlnode = xdoc.SelectNodes(NodeHier);
                    foreach (XmlNode node1 in xmlnode)
                    {
                        foreach (XmlNode node2 in node1)
                        {
                            ColumnListItem = new ListViewItem(node2.Name.ToString());
                            ColumnList.Items.Add(ColumnListItem);


                        }
                        break;
                    }
                    listView1.Clear();

                    listView1.View = View.Details;


                    foreach (XmlNode node1 in xmlnode)
                    {
                        foreach (XmlNode node2 in node1)
                        {
                            if (node2.Name == ColumnList.Items[0].Text)
                            {
                                ColumnListItem = new ListViewItem(node2.InnerText.ToString());
                            }
                            else
                                ColumnListItem.SubItems.Add(node2.InnerText.ToString());



                        }
                        listView1.Items.Add(ColumnListItem);


                    }

                    for (int i = 0; i < ColumnList.Items.Count; i++)
                    {
                        ColumnHeader colHead = new ColumnHeader();
                        colHead.Text = "";
                        colHead.Text = ColumnList.Items[i].Text;

                        listView1.Columns.Add(colHead);






                    }
                    for (int i = 0; i < listView1.Columns.Count; i++)
                    {
                        listView1.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
                        listView1.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
                    }

                    /*foreach (XmlNode tag in xmlnode)
                    {
                        XmlNodeList xmlnode2 = xdoc.GetElementsByTagName("FileList");
                        foreach (XmlNode tag2 in xmlnode2)
                        {

                            foreach (XmlNode tag3 in tag2)
                            {

                                if (tag3.Name == "FileName")
                                    lt = new ListViewItem(tag3.InnerText.ToString());
                                else
                                    lt.SubItems.Add(tag3.InnerText);


                            }
                            //    lt = new ListViewItem(tag2.InnerText);
                            //if (tag2.Name == "Status")
                            //    lt.SubItems.Add(tag2.InnerText);
                            //if (tag2.Name == "FilePath")
                            //    lt.SubItems.Add(tag2.InnerText);
                            listView1.Items.Add(lt);

                        }

                    }*/

                }
                else
                    MessageBox.Show("'" + TableName + "' table is not available");
                //MessageBox.Show("Valid QUery");
            }
            else
                MessageBox.Show("Invalid Query");
            
        }
        
        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }
        public void setPath(string DirPath,string DirName)
        {
            ServerPath = DirPath;
            ServerName = DirName;
            ImageList imgList = new ImageList();
            imgList.Images.Add(Properties.Resources.dbb);
            imgList.Images.Add(Properties.Resources.table);
            treeView1.ImageList = imgList;
            TreeNode rootNode = new TreeNode(DirName);
            rootNode.ImageIndex = 0;
            rootNode.SelectedImageIndex = 0;
            treeView1.Nodes.Add(rootNode);
            DirectoryInfo ds = new DirectoryInfo(DirPath);
            FileInfo[] TableList = ds.GetFiles("*.xml");
            for (int i = 0; i < TableList.Length; i++)
            {
                TreeNode childNode = new TreeNode();
                childNode.Text = TableList[i].Name.Substring(0,TableList[i].Name.IndexOf(".xml"));
                childNode.ImageIndex = 1;
                childNode.SelectedImageIndex = 1;
                treeView1.Nodes[0].Nodes.Add(childNode);
            }
        }

        private void fIleToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            XQLCreateTable xqlct = new XQLCreateTable();
            bool Create = true;
            xqlct.LoadServerInfo(ServerName, ServerPath, Create);
            xqlct.ShowDialog();
            treeView1.Nodes.Clear();
            setPath(ServerPath, ServerName);
            
        }

        private void XQLManager_Load(object sender, EventArgs e)
        {
           
        }

        private void treeView1_MouseClick(object sender, MouseEventArgs e)
        {
            //if (e.Button == MouseButtons.Right)
            //{
            //    TreeNode Node = treeView1.GetNodeAt(e.X, e.Y);
            //    string TableNme = Node.Text;
                
            //}
            
        }

        private void execQuery_Click_1(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void queryTxtBox_TextChanged_1(object sender, EventArgs e)
        {

        }

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {

            if (treeView1.SelectedNode != null)
            {
                string EditTableName = treeView1.SelectedNode.Text;
                XQLCreateTable xct = new XQLCreateTable();
                bool Create = false;
                xct.LoadServerInfo(ServerName, ServerPath, Create);
                bool flag = xct.EditLoadInfo(EditTableName);
                if (flag) 
                    xct.ShowDialog();
                treeView1.Nodes.Clear();
                setPath(ServerPath, ServerName);
            }
            else
                MessageBox.Show("Please select a table!");
        }

        private void treeView1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                TreeNode Node = treeView1.GetNodeAt(e.X, e.Y);
                string TableNme = Node.Text;
                if (TableNme == ServerName)
                    return;
                queryTxtBox.Text = "select * from " + TableNme;
            }
        }

        private void exitBtn_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    
    }
}
